﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Configuration;
using LibraryONTrack.Common;
using LibraryONTrack.Persistence.DataMappers;
using System.Reflection;
using System.IO;
using System.Windows.Forms;


namespace LibraryONTrack.dao
{
	[Serializable]
	public class SqlDaoException : Exception
	{
		public SqlDaoException() { }
		public SqlDaoException(string message) : base(message) { }
		public SqlDaoException(string message, Exception inner) : base(message, inner) { }
		protected SqlDaoException(
		System.Runtime.Serialization.SerializationInfo info,
		System.Runtime.Serialization.StreamingContext context)
			: base(info, context) { }
	}

	public class SqlDao
	{

		
		#region "Database Helper Methods"
		public string DBVersion { get; private set; }
		public string DBCreateTime { get; private set; }
		public string DBPath { get; private set; }
		private SQLiteConnection _currentConnection = null;
		public SQLiteConnection CurrentConnection
		{ 
			get
			{
				if (_currentConnection == null)
				{
					_currentConnection = SharedConnection;
				}
				if (_currentConnection == null)
				{
					throw new SqlDaoException("No Connection Defined");
				}
				return _currentConnection;
			}
			private set
			{
				_currentConnection = value;
			}

		}
		static private SQLiteConnection SharedConnection = null;
		

		// Connection
		public SQLiteConnection SharedSQLiteConnection { get; set; }
		
		public SqlDao(string dbPath, bool shareConnection) : this()
		{
			if (Path.GetExtension(dbPath) == "")
			{
				dbPath = Path.ChangeExtension(dbPath, ".s3db");
			}
			DBPath = dbPath;
			string connectionString = "Data Source="+DBPath+";Version=3;Pooling=False;Max Pool Size=1;";
			SaveConnection(new SQLiteConnection(connectionString), shareConnection);

		}

		private void SaveConnection(SQLiteConnection connection, bool shareConnection)
		{
			CurrentConnection = connection;
			if (shareConnection)
			{
				SharedConnection = CurrentConnection;
			}
		}

		// Constructors
		public SqlDao()
		{
		}

		public SqlDao(SQLiteConnection connection, bool shareConnection)
			: this()
		{
			SaveConnection(connection, shareConnection);			
		}

		internal string GetFromResources(string resourceName)
		{
			Assembly assem = this.GetType().Assembly;
			using (Stream stream = assem.GetManifestResourceStream(resourceName))
			{
				using (var reader = new StreamReader(stream))
				{
					return reader.ReadToEnd();
				}
			}
		}

			

		/// <summary>
		/// Creates an empty database in the named path
		/// </summary>
		/// <param name="dbPath"></param>
		public void InitializeDB(bool installSampleData)
		{

			File.Delete(DBPath);

				SQLiteConnection.CreateFile(DBPath);
			string text = GetFromResources("LibraryONTrack.Resources.DatabaseScripts.LOTSchema.txt");
			SqlDao dao = new SqlDao();
			dao.ExecuteNonQuery(dao.GetSQLiteCommand(text));

			if (installSampleData)
			{
				text = GetFromResources("LibraryONTrack.Resources.DatabaseScripts.rosterdata.sql");
				dao.ExecuteNonQuery(dao.GetSQLiteCommand(text));

				text = GetFromResources("LibraryONTrack.Resources.DatabaseScripts.booksdata.sql");
				dao.ExecuteNonQuery(dao.GetSQLiteCommand(text));
			}
		}

		public void RestoreDB()
		{
			throw new NotImplementedException();
		}

		public void CheckDB()
		{
			SQLiteCommand cmd = null;
			try
			{
				cmd = GetSQLiteCommand("PRAGMA integrity_check;");
				string result = (string)ExecuteScalar(cmd);
				if (result != "ok")
				{
					throw new SqlDaoException(result);
				}
				
				cmd.CommandText = "SELECT * FROM version;";

				if (cmd.Connection.State != ConnectionState.Open)
				{
					cmd.Connection.Open();
				}
				SQLiteDataReader reader = cmd.ExecuteReader();
				if (reader.HasRows)
				{
					reader.Read();
					string DBVersion = reader.GetString(0);
					string DBCreateTime = reader.GetString(1);
					reader.Close();
				}
			}
			catch (Exception e)
			{
				throw new SqlDaoException(e.Message, e);
			}
			finally
			{
				cmd.Connection.Close();
			}
		}


		// GetDbSQLiteCommand
		public SQLiteCommand GetSQLiteCommand(string sqlQuery)
		{
			SQLiteCommand command = new SQLiteCommand();
			command.Connection = SharedConnection;
			command.CommandType = CommandType.Text;
			command.CommandText = sqlQuery;
			return command;
		}

		// GetDbSprocCommand
		public SQLiteCommand GetSprocCommand(string sprocName)
		{
			SQLiteCommand command = new SQLiteCommand(sprocName);
			command.Connection = SharedConnection;
			command.CommandType = CommandType.StoredProcedure;
			return command;
		}

		// CreateNullParameter
		public SQLiteParameter CreateNullParameter(string name, DbType paramType)
		{
			SQLiteParameter parameter = new SQLiteParameter();
			parameter.DbType = paramType;
			parameter.ParameterName = name;
			parameter.Value = DBNull.Value;
			parameter.Direction = ParameterDirection.Input;
			return parameter;
		}

		// CreateNullParameter - with size for nvarchars
		public SQLiteParameter CreateNullParameter(string name, DbType paramType, int size)
		{
			SQLiteParameter parameter = new SQLiteParameter();
			parameter.DbType = paramType;
			parameter.ParameterName = name;
			parameter.Size = size;
			parameter.Value = DBNull.Value;
			parameter.Direction = ParameterDirection.Input;
			return parameter;
		}

		// CreateOutputParameter
		public SQLiteParameter CreateOutputParameter(string name, DbType paramType)
		{
			SQLiteParameter parameter = new SQLiteParameter();
			parameter.DbType = paramType;
			parameter.ParameterName = name;
			parameter.Direction = ParameterDirection.Output;
			return parameter;
		}

		// CreateOuputParameter - with size for nvarchars
		public SQLiteParameter CreateOutputParameter(string name, DbType paramType, int size)
		{
			SQLiteParameter parameter = new SQLiteParameter();
			parameter.DbType = paramType;
			parameter.Size = size;
			parameter.ParameterName = name;
			parameter.Direction = ParameterDirection.Output;
			return parameter;
		}

		// CreateParameter - uniqueidentifier
		public SQLiteParameter CreateParameter(string name, Guid value)
		{
			if (value.Equals(NullValues.NullGuid))
			{
				// If value is null then create a null parameter
				return CreateNullParameter(name, DbType.StringFixedLength);
			}
			else
			{
				SQLiteParameter parameter = new SQLiteParameter();
				parameter.DbType = DbType.StringFixedLength;
				parameter.ParameterName = name;
				parameter.Value = value.ToString();
				parameter.Direction = ParameterDirection.Input;
				return parameter;
			}
		}

		public SQLiteParameter CreateParameter(string name, string value, int size)
		{
			if (String.IsNullOrEmpty(value))
			{
				// If value is null then create a null parameter
				return CreateNullParameter(name, DbType.StringFixedLength);
			}
			else
			{
				SQLiteParameter parameter = new SQLiteParameter();
				parameter.DbType = DbType.StringFixedLength;
				parameter.Size = size;
				parameter.ParameterName = name;
				parameter.Value = value;
				parameter.Direction = ParameterDirection.Input;
				return parameter;
			}
		}

		// CreateParameter - int
		public SQLiteParameter CreateParameter(string name, long value)
		{
			if (value == NullValues.NullLong)
			{
				// If value is null then create a null parameter
				return CreateNullParameter(name, DbType.Int64);
			}
			else
			{
				SQLiteParameter parameter = new SQLiteParameter();
				parameter.DbType = DbType.Int64;
				parameter.ParameterName = name;
				parameter.Value = value;
				parameter.Direction = ParameterDirection.Input;
				return parameter;
			}
		}


		public static DbType TypeToDbType(Type t)
		{
			DbType dbt;
			try
			{
				dbt = (DbType)Enum.Parse(typeof(DbType), t.Name);
			}
			catch
			{ 
				dbt = DbType.Object;
			}
			return dbt;
		}

		// CreateParameter - bool
		public SQLiteParameter CreateParameter(string name, bool value)
		{
			SQLiteParameter parameter = new SQLiteParameter();
			parameter.DbType = DbType.Int32;
			parameter.ParameterName = name;
			parameter.Value = value ? 1 : 0;
			parameter.Direction = ParameterDirection.Input;
			return parameter;
		}

		#endregion

		#region "Data Projection Methods"

		// ExecuteNonQuery
		public void ExecuteNonQuery(SQLiteCommand command)
		{
			try
			{
				if (command.Connection.State != ConnectionState.Open)
				{
					command.Connection.Open();
				}
				command.ExecuteNonQuery();
			}
			catch (Exception e)
			{
				throw new Exception("Error executing query", e);
			}
			finally
			{
				command.Connection.Close();
			}
		}

		// ExecuteScalar
		public Object ExecuteScalar(SQLiteCommand command)
		{
			try
			{
				if (command.Connection.State != ConnectionState.Open)
				{
					command.Connection.Open();
				}
				return command.ExecuteScalar();
			}
			catch (Exception e)
			{
				throw new Exception("Error executing query", e);
			}
			finally
			{
				command.Connection.Close();
			}
		}

		// GetSingleValue
		public T GetSingleValue<T>(SQLiteCommand command)
		{
			T returnValue = default(T);
			try
			{
				if (command.Connection.State != ConnectionState.Open)
				{
					command.Connection.Open();
				}
				SQLiteDataReader reader = command.ExecuteReader();
				if (reader.HasRows)
				{
					reader.Read();
					if (!reader.IsDBNull(0)) { returnValue = (T)reader[0]; }
					reader.Close();
				}
			}
			catch (Exception e)
			{
				throw new Exception("Error populating data", e);
			}
			finally
			{
				command.Connection.Close();
			}
			return returnValue;
		}

		// GetSingleInt32
		public Int32 GetSingleInt32(SQLiteCommand command)
		{
			Int32 returnValue = default(int);
			try
			{
				if (command.Connection.State != ConnectionState.Open)
				{
					command.Connection.Open();
				}
				SQLiteDataReader reader = command.ExecuteReader();
				if (reader.HasRows)
				{
					reader.Read();
					if (!reader.IsDBNull(0)) { returnValue = reader.GetInt32(0); }
					reader.Close();
				}
			}
			catch (Exception e)
			{
				throw new Exception("Error populating data", e);
			}
			finally
			{
				command.Connection.Close();
			}
			return returnValue;
		}

		// GetSingleString
		public string GetSingleString(SQLiteCommand command)
		{
			string returnValue = null;
			try
			{
				if (command.Connection.State != ConnectionState.Open)
				{
					command.Connection.Open();
				}
				SQLiteDataReader reader = command.ExecuteReader();
				if (reader.HasRows)
				{
					reader.Read();
					if (!reader.IsDBNull(0)) { returnValue = reader.GetString(0); }
					reader.Close();
				}
			}
			catch (Exception e)
			{
				throw new Exception("Error populating data", e);
			}
			finally
			{
				command.Connection.Close();
			}
			return returnValue;
		}


		// GetGuidList
		public List<Guid> GetGuidList(SQLiteCommand command)
		{
			List<Guid> returnList = new List<Guid>();
			try
			{
				if (command.Connection.State != ConnectionState.Open)
				{
					command.Connection.Open();
				}
				SQLiteDataReader reader = command.ExecuteReader();
				if (reader.HasRows)
				{
					returnList = new List<Guid>();
					while (reader.Read())
					{
						if (!reader.IsDBNull(0)) { returnList.Add(reader.GetGuid(0)); }
					}
					reader.Close();
				}
			}
			catch (Exception e)
			{
				throw new Exception("Error populating data", e);
			}
			finally
			{
				command.Connection.Close();
			}
			return returnList;
		}


		// GetStringList
		public List<string> GetStringList(SQLiteCommand command)
		{
			List<string> returnList = new List<string>();
			try
			{
				if (command.Connection.State != ConnectionState.Open)
				{
					command.Connection.Open();
				}
				SQLiteDataReader reader = command.ExecuteReader();
				if (reader.HasRows)
				{
					returnList = new List<string>();
					while (reader.Read())
					{
						if (!reader.IsDBNull(0)) { returnList.Add(reader.GetString(0)); }
					}
					reader.Close();
				}
			}
			catch (Exception e)
			{
				throw new Exception("Error populating data", e);
			}
			finally
			{
				command.Connection.Close();
			}
			return returnList;
		}


		// GetSingle
		public T GetSingle<T>(SQLiteCommand command) where T : class
		{
			T dto = null;
			try
			{
				if (command.Connection.State != ConnectionState.Open)
				{
					command.Connection.Open();
				}
				SQLiteDataReader reader = command.ExecuteReader();
				if (reader.HasRows)
				{
					reader.Read();
					IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));
					dto = (T)mapper.GetData(reader);
					reader.Close();
				}
			}
			catch (Exception e)
			{
				throw new Exception("Error populating data", e);
			}
			finally
			{
				command.Connection.Close();
			}
			// return the DTO, it's either populated with data or null.
			return dto;
		}


		// GetList
		public List<T> GetList<T>(SQLiteCommand command) where T : class
		{
			List<T> dtoList = new List<T>();
			try
			{
				if (command.Connection.State != ConnectionState.Open)
				{
					command.Connection.Open();
				}
				SQLiteDataReader reader = command.ExecuteReader();
				if (reader.HasRows)
				{
					IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));
					while (reader.Read())
					{
						T dto = null;
						dto = (T)mapper.GetData(reader);
						dtoList.Add(dto);
					}
					reader.Close();
				}
			}
			catch (Exception e)
			{
				throw new Exception("Error populating data", e);
			}
			finally
			{
				command.Connection.Close();
			}
			// We return either the populated list if there was data,
			// or if there was no data we return an empty list.
			return dtoList;
		}




		// GetDataPage
		public DataPage<T> GetDataPage<T>(SQLiteCommand command, int pageIndex, int pageSize) where T : class
		{
			DataPage<T> page = new DataPage<T>();
			page.PageIndex = pageIndex;
			page.PageSize = pageSize;
			try
			{
				if (command.Connection.State != ConnectionState.Open)
				{
					command.Connection.Open();
				}
				SQLiteDataReader reader = command.ExecuteReader();
				if (reader.HasRows)
				{
					IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));
					while (reader.Read())
					{
						// get the data for this row
						T dto = null;
						dto = (T)mapper.GetData(reader);
						page.Data.Add(dto);
						// If we haven't set the RecordCount yet then set it
						if (page.RecordCount == 0) { page.RecordCount = mapper.GetRecordCount(reader); }
					}
					reader.Close();
				}
			}
			catch (Exception e)
			{
				throw new Exception("Error populating data", e);
			}
			finally
			{
				command.Connection.Close();
			}
			return page;
		}

		#endregion
	
	}
}
